package com.finance.cooperate.dao.repository.primary;

import com.finance.cooperate.dao.entity.primary.Borrowing;
import com.finance.cooperate.dao.support.dto.BorrowingApplyDTO;
import com.finance.cooperate.dao.support.jpa.BaseRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * @InterfaceName BorrowingRepository
 * @Description 账单仓库
 * @Author shen
 * @Date 2023/8/6 22:43
 * @Modify ...
 */
@Repository
public interface BorrowingRepository extends BaseRepository<Borrowing, String> {

    @Query(value = "SELECT * FROM app_borrowing p WHERE p.repayment_time is not null and p.user_id = ?1 and p.number < ?2 order by p.number desc, p.create_time desc limit 1", nativeQuery = true)
    Borrowing getLastBorrowing(String uid, Long number);


    @Query(value = "SELECT b.id, b.number, b.overdue_day, b.select_amount, b.payoff_time, b.repayment_time, b.create_time, case when bb.payoff_time IS NOT NULL then bb.payoff_time else a.through_time end as through_time FROM app_borrowing b LEFT JOIN app_borrowing bb ON b.apply_id = bb.apply_id AND b.number = bb.number + 1 and bb.repayment_time is not null LEFT JOIN app_apply a ON b.apply_id = a.id WHERE b.repayment_time is not null and b.user_id = ?1 and b.number < ?2 ORDER BY b.number desc", nativeQuery = true)
    List<BorrowingApplyDTO> getBorrowLt(String uid, Integer number);

    @Query(value = "SELECT COUNT(1) FROM app_borrowing b LEFT JOIN app_apply a ON b.apply_id = a.id WHERE b.repayment_time BETWEEN DATE_SUB(?1, INTERVAL 3 MONTH) AND ?1 AND a.ip = ?2 AND overdue_day > 0 AND b.user_id != ?3", nativeQuery = true)
    Integer sameIpOverdueCount(Date lastSubmitTime, String ip, String uid);



}
